Introduktion

Den här guiden hjälper dig att komma åt SLU:s Basreg-databas (basregDWH) med R och RStudio.

Du kommer att lära dig att:

En beskrivning av Rstudios användargränssnitt finns på Posits hemsida (Posit utvecklar Rstudio): https://docs.posit.co/ide/user/ide/guide/ui/ui-panes.html

Om du läser den här dokumentationen med avsikten att använda den för dataanalys, se till att du förstår hur R‑projekt fungerar: https://docs.posit.co/ide/user/ide/guide/code/projects.html

Viktigt: Användare har läsbehörighet (read-only).
Det betyder att du kan titta på och ladda ner data, men du riskerar inte att ändra databasen.

Programvaruförkunskaper

Du behöver:

  1. RStudio
    Official getting started guide:
    https://docs.posit.co/ide/user/ide/get-started/

  2. Åtkomst till SLU Basreg / BasregDWH
    För behörighet, kontakta: Eva Rundlöf

  3. Antingen vara på ett SLU-campus eller använda SLU:s VPN
    Så använder du VPN:
    https://internt.slu.se/en/support-services/administrative-support/it/support/guider-manualer/vpn-anyconnect/

Om du har en Windows-dator från SLU verifierar ODBC normalt din identitet automatiskt vid anslutning. Linux- och Mac-användare behöver följa en separat instruktion.

Valfritt: Git + GitHub (rekommenderas)

Om du vill hålla ditt arbete organiserat kan du använda Git direkt i RStudio:
https://docs.posit.co/ide/user/ide/guide/tools/version-control.html

Ladda ner guiden från GitHub

Repo:
https://github.com/TKlingstrom/Basreg_introduction

Du kan ladda ner det på två sätt:

Alternativ A: Klona med Git i RStudio (rekommenderas)

  1. I RStudio: File → New Project → Version Control → Git
  2. Klistra in repo-URL:en
  3. Välj en mapp och klicka på Create Project

Alternativ B: Ladda ner som ZIP

  1. Öppna repo:t i din webbläsare
  2. Klicka på Code → Download ZIP
  3. Packa upp och öppna .Rproj-filen i RStudio

Arbeta med den här guiden

Helst följer du guiden direkt i R Markdown. För att göra det öppnar du basreg_userguide.Rmd i RStudio eller öppnar RStudio och väljer File → Open project in new session. Den svenska versionen ligger i en mapp och du öppnar projektfilen och markdownfilen i den foldern istället.

Du kan också skapa ett eget R‑script (i RStudio: File → New File → R-script) och kopiera koden från HTML-filen (eller PDF:en).

Skillnaden mellan R Markdown och ett R‑script är att du i R Markdown kan köra och köra om din kod i “chunks”. Chunks är gråmarkerade och har en liten grön play‑pil uppe till höger. Utdata från att köra koden visas direkt under koden. R Markdown kan också enkeltanvändas för att skapa en PDF- eller HTML-fil. Jag rekommenderar att du följer den här guiden direkt i R Markdown-filen eller, alternativt, öppnar HTML-filen i din webbläsare och sedan skriver in koden i ett R‑script. I R Markdown-filen kan du scrolla ned tills du hittar den här texten och sedan börja handledningen nedan. Varje kodchunk måste köras minst en gång (antingen genom att klicka på den gröna play-knappen uppe till höger i chunken, eller genom att kopiera koden till ditt R‑script, markera den och trycka Ctrl + Enter). Du kan ändra koden och köra den igen som du vill; namnet till vänster om <- blir namnet på objektet som skapas och kan nås i fliken Environment (övre högra panelen).

Vi använder följande paket som måste installeras och laddas (kör koden nedan):

  • odbc: ODBC‑drivrutin/anslutning till databas
  • DBI: databasgränssnitt
  • dplyr: datahantering
  • dbplyr: gör att dplyr fungerar med databaser
  • ggplot2: används senare för att plotta data

Installera paket (behövs bara en gång)

install.packages(c("odbc", "DBI", "dplyr", "dbplyr", "ggplot2"))

Ladda paket (behövs varje session)

library(odbc)
library(DBI)
library(dplyr)
library(dbplyr)
library(ggplot2)

Kontrollera din R-session

Kontrollera din aktuella arbetskatalog

Här läser/skriver R filer (t.ex. exporter).

getwd()

För att byta mapp:

#If necessary, remove the # below and set your working directory to the Basreg_introduction folder
#setwd("C:/path/to/your/folder")

Steg 1: Anslut till BasregDWH

Den här chunken skapar ett anslutningsobjekt som heter con.

Tänk på con som sättet att tala om för din RStudio‑session att ansluta till Basreg‑servern.

Du laddar inte ner hela databasen till din dator.
I stället skickar du förfrågningar till servern och hämtar data från SQL‑vyer.

En vy är ett sätt att presentera information i en SQL‑server och innehåller främst observationsdata (händelser, mätningar). På grund av hur Basreg har utvecklats innehåller vyerna också vissa mindre användbara kolumner som behövs för andra sätt att interagera med Basreg.

# Connecting to the database using your R/Windows user credentials
con <- dbConnect(
  odbc(),
  Driver = "SQL Server",
  Server = "basregdwh.db.slu.se",
  Database = "basregDWH"
)

# List objects available in the apiSci schema
odbcListObjects(con, catalog = "basregDWH", schema = "apiSci")

Steg 2: Förhandsgranska en vy (ladda ner bara några rader)

Funktionen tbl() skapar en referens till en vy (view) som anges i parentesen.

Tack vare dbplyr kan du filtrera eller bearbeta data innan du laddar ner den. I det här fallet:

%>% är sättet du anger de olika steg som dplyr och dbplyr ska utföra i en pipe när du filtrerar eller bearbetar data. Om du skriver DF.Cow efter pipen visas resultatet för dig. I fliken Environment (övre högra panelen i RStudio) ser du också alla objekt du skapar. Om du klickar på den blå cirkeln framför ett objekt visas datatypen, och om du klickar på tabell-ikonen längst till höger öppnas en tabellvy med innehållet i dataframen. Fliken Connections (till höger om Environment) visar också alla databasscheman du kan komma åt; om du har behörighet kan du klicka på den blå cirkeln och se vad som finns i schemat. Vyerna vi kommer att arbeta med finns under basregDWH → apiSci och börjar med “fact”.

DF.Cow <- con %>%
  tbl(in_catalog("basregDWH", "apiSci", "factCattleView")) %>%
  head(10) %>%
  collect()

DF.Cow

Prova att byta objektnamn i koden ovan, till exempel:

Steg 3: Filtrera rader (glesa vyer är vanliga)

Många Basreg-vyer är glesa (sparse).

Det betyder att de flesta rader bara har några få ifyllda kolumner och många NA.

Filtrering hjälper dig att välja ut bara meningsfulla rader.

Exempel:

DF.Reproduction <- con %>%
  tbl(in_catalog("basregDWH", "apiSci", "factCattleReproductionView")) %>%
  filter(!is.na(Calving)) %>%
  collect()

DF.Reproduction

Steg 4: Flera filter (ALLA måste vara sanna)

Du kan kombinera filter.

I filter() betyder flera villkor:

villkor 1 OCH villkor 2 OCH villkor 3

Här väljer vi:

DF.Reproduction <- con %>%
  tbl(in_catalog("basregDWH", "apiSci", "factCattleReproductionView")) %>%
  filter(
    !is.na(Calving),
    StartDate >= as.Date("2021-01-01"),
    StartDate <= as.Date("2021-01-31")
  ) %>%
  collect()

DF.Reproduction

Steg 5: ELLER-filter (antingen villkoret kan vara sant)

Ibland vill du ha antingen:

I R:

Här väljer vi:

DF.Reproduction <- con %>%
  tbl(in_catalog("basregDWH", "apiSci", "factCattleReproductionView")) %>%
  filter(
    (!is.na(Calving) | !is.na(DryOff)),
    StartDate >= as.Date("2021-01-01"),
    StartDate <= as.Date("2021-01-31")
  ) %>%
  collect()

DF.Reproduction

Steg 6: Välj kolumner (ladda bara ner det du behöver)

I Basreg-vyer finns det ofta många kolumner. Funktionen select() behåller bara de kolumner du anger. Allt annat tas bort från det som laddas ner.

Här återanvänder vi samma fråga som i Steg 5 (Calving eller DryOff under januari 2021), men hämtar bara de viktigaste kolumnerna.

DF.Reproduction <- con %>%
  tbl(in_catalog("basregDWH", "apiSci", "factCattleReproductionView")) %>%
  filter(
    (!is.na(Calving) | !is.na(DryOff)),
    StartDate >= as.Date("2021-01-01"),
    StartDate <= as.Date("2021-01-31")
  ) %>%
  select(Farm, SE_Number, StartDate, Calving, CalvingEase, DryOff) %>%
  collect()

DF.Reproduction

Steg 7: Ändra/bygga om kolumner

Nu kommer vi att bearbeta vyn innan vi laddar ner den från servern.

I chunken nedan tar vi bort kolumnerna Calving, DryOff och StartDate och skapar i stället (med mutate()):

Det gör datamängden lättare att tolka efter nedladdning.

DF.Reproduction <- con %>%
  tbl(in_catalog("basregDWH", "apiSci", "factCattleReproductionView")) %>%
  filter(
    (!is.na(Calving) | !is.na(DryOff)),
    StartDate >= as.Date("2021-01-01"),
    StartDate <= as.Date("2021-01-31")
  ) %>%
  mutate(
    # mutate() creates new columns (or modifies existing ones)
    # Read more here: https://dplyr.tidyverse.org/reference/mutate.html
    CalvingDate = if_else(!is.na(Calving), StartDate, as.Date(NA)),
    DryOffDate  = if_else(!is.na(DryOff),  StartDate, as.Date(NA))
  ) %>%
  select(Farm, SE_Number, CalvingDate, DryOffDate, CalvingEase) %>%
  collect()

DF.Reproduction

Steg 8: Kombinera information från flera vyer

Reproduktionsvyn talar om när en kalvning skedde, men den visar inte direkt vilken avkomma som föddes vid den händelsen.

För att koppla kalvningen till avkommans identitet kombinerar vi två vyer:

I factCattleView lagras moderns SE-nummer i kolumnen Mother.

Vi kommer att:

  1. Plocka ut Farm, SE_Number, CalvingDate och CalvingEase från factCattleReproductionView
  2. Plocka ut SE_Number (avkomma), Mother och BirthDate från factCattleView
  3. Matcha kalvningar genom att joina:
    • SE_Number (moder) = Mother
    • CalvingDate = BirthDate
  4. Behålla kalvningsinformationen och döpa om avkommans SE_Number till Offspring
# IMPORTANT:
# In this step we do NOT download (collect) the full views into R.
# Instead, dbplyr keeps the objects as "lazy" SQL queries that remain on the Basreg server.
# This means filter(), transmute() and inner_join() are translated into SQL and run server-side.
# Only when we call collect() at the end will the result be downloaded into R.
# Running computations server-side is appropriate when it reduces the amount of data you need to download. Running large computations serverside
# should however be avoided as it may impact other users.


#
# Read more here:
# - dbplyr basics (lazy queries): https://dbplyr.tidyverse.org/articles/dbplyr.html
# - dbplyr translation to SQL:   https://dbplyr.tidyverse.org/articles/translation.html

# 1) Prepare the calving events table (still server-side, NOT collected)
DF.ReproductionEvents <- con %>%
  tbl(in_catalog("basregDWH", "apiSci", "factCattleReproductionView")) %>%
  filter(
    !is.na(Calving),
    StartDate >= as.Date("2021-01-01"),
    StartDate <= as.Date("2021-01-31")
  ) %>%
  transmute(
    Farm = Farm,
    SE_Number = SE_Number,        # Mother identity in reproduction view
    CalvingDate = StartDate,      # Calving date is stored in StartDate
    CalvingEase = CalvingEase
  )

# 2) Prepare the cattle view with offspring information (still server-side, NOT collected)
DF.Cattle <- con %>%
  tbl(in_catalog("basregDWH", "apiSci", "factCattleView")) %>%
  transmute(
    Offspring = SE_Number,        # Offspring identity in cattle view
    Mother = Mother,              # Mother identity in cattle view
    BirthDate = BirthDate
  )

# 3) Join the two server-side objects in SQL Server and sort from first born to last born
DF.CalvingWithOffspring <- DF.ReproductionEvents %>%
  inner_join(
    DF.Cattle,
    by = c("SE_Number" = "Mother", "CalvingDate" = "BirthDate")
  ) %>%
  arrange(CalvingDate) %>%  # arrange() sorts the output by date (oldest first)
  select(Farm, SE_Number, Offspring, CalvingDate, CalvingEase) %>%
  collect()  # collect() runs the SQL query and downloads ONLY the final joined result into R

# 4) List any calving events that did not find an offspring match
DF.UnmatchedCalvings <- DF.ReproductionEvents %>%
  anti_join(
    DF.Cattle,
    by = c("SE_Number" = "Mother", "CalvingDate" = "BirthDate")
  ) %>%
  arrange(SE_Number, CalvingDate) %>%
  collect()

DF.CalvingWithOffspring #This table will contain all cases where we now have a Mother and a SE_Number for the calf.
DF.UnmatchedCalvings  #This table will contain all cases where we could not find a Cow born on the right day with a mother giving birth that day.

Steg 9: Välj mjölkningsposter efter varje kalvning

Nu använder vi kalvningstabellen som vi skapade i Steg 8 för att hitta mjölkningsposter efter varje kalvning.

För varje moder (SE_Number) och varje CalvingDate i kalvningstabellen väljer vi rader från factCattleMilkingView där mjölkningsdatumet (StartDate) ligger inom 365 dagar efter kalvningsdatumet.

För att göra detta effektivt håller vi arbetet server-side och joinar vyerna i SQL Server. Först när vi använder collect() laddas det slutliga resultatet ner.

# IMPORTANT:
# We recreate the calving table as a server-side query (not collected),
# then join it to the milking view on the SQL server.
# This avoids looping in R and avoids downloading large views.
#
# Read more here:
# - dbplyr joins: https://dbplyr.tidyverse.org/articles/two-table.html
# - dbplyr SQL translation: https://dbplyr.tidyverse.org/articles/translation.html

# 1) Calving table (server-side, NOT collected)
DF.CalvingWithOffspring_db <- con %>%
  tbl(in_catalog("basregDWH", "apiSci", "factCattleReproductionView")) %>%
  filter(
    !is.na(Calving),
    StartDate >= as.Date("2021-01-01"),
    StartDate <= as.Date("2021-01-31")
  ) %>%
  transmute(
    Farm = Farm,
    SE_Number = SE_Number,     # Mother
    CalvingDate = StartDate,
    CalvingEase = CalvingEase
  ) %>%
  inner_join(
    con %>% tbl(in_catalog("basregDWH", "apiSci", "factCattleView")) %>%
      transmute(
        Offspring = SE_Number,
        Mother = Mother,
        BirthDate = BirthDate
      ),
    by = c("SE_Number" = "Mother", "CalvingDate" = "BirthDate")
  ) %>%
  select(Farm, SE_Number, Offspring, CalvingDate, CalvingEase)

# 2) Milking view (server-side, NOT collected)
DF.Milking <- con %>%
  tbl(in_catalog("basregDWH", "apiSci", "factCattleMilkingView"))

# 3) Join calvings to milking records and keep only milking within 365 days after each calving
DF.MilkingAfterCalving <- DF.CalvingWithOffspring_db %>%
  inner_join(DF.Milking, by = "SE_Number") %>%  # match milking records to the mother
  filter(
    StartDate >= CalvingDate,
    StartDate < sql("DATEADD(day, 365, CalvingDate)") #This is where the 365 day limit is kept
  ) %>%
  arrange(SE_Number, CalvingDate, StartDate) %>%  # sort from earliest to latest
  collect()  # download only the filtered result to R

DF.MilkingAfterCalving

Steg 10: Göra beräkningar på data

Nu sammanfattar vi mjölkningsposterna som vi valde i Steg 9.

Vi grupperar datamängden efter CalvingEase och beräknar:

Eftersom kor kan mjölkas flera gånger per dag kan tabellen factCattleMilkingView innehålla flera rader per ko och dag. I så fall är TotalYield oftast avkastningen per mjölkning, inte den totala dagsavkastningen. För att få en korrekt dagsavkastning summerar vi först alla mjölkningar inom samma dag för varje ko och beräknar sedan den genomsnittliga dagsavkastningen.

För att bättre beskriva variation redovisar vi även median och kvartiler (25% och 75%).

# This step uses group_by() and summarise() to calculate statistics.
# Read more here:
# - group_by():  https://dplyr.tidyverse.org/reference/group_by.html
# - summarise(): https://dplyr.tidyverse.org/reference/summarise.html
#
# IMPORTANT:
# In factCattleMilkingView there can be multiple rows per day (multiple milkings).
# TotalYield is then usually the yield per milking, not per day.
# To calculate DAILY yield, we first sum TotalYield within each day for each cow.

# 1) Calculate daily total yield per cow (sums multiple milkings on the same date)
DF.DailyMilkPerCow <- DF.MilkingAfterCalving %>%
  group_by(CalvingEase, SE_Number, StartDate) %>%
  summarise(
    DailyTotalYield = sum(TotalYield, na.rm = TRUE), # sum yield across milkings that day
    MilkingsPerDay = n(),                            # number of milkings that day
    .groups = "drop"
  )

DF.DailyMilkPerCow
# 2) Summarise per cow (within each CalvingEase group)
DF.MilkingPerCow <- DF.DailyMilkPerCow %>%
  group_by(CalvingEase, SE_Number) %>%
  summarise(
    MilkingDays = n_distinct(StartDate),                   # number of days with milking records
    AvgDailyYieldCow = mean(DailyTotalYield, na.rm = TRUE),# average DAILY yield per cow
    .groups = "drop"
  )

DF.MilkingPerCow
# 3) Summarise per CalvingEase group (mean, median, quartiles)
DF.SummaryByCalvingEase <- DF.MilkingPerCow %>%
  group_by(CalvingEase) %>%
  summarise(
    Cows = n_distinct(SE_Number),

    AvgMilkingDaysPerCow = mean(MilkingDays, na.rm = TRUE),
    MedianMilkingDaysPerCow = median(MilkingDays, na.rm = TRUE),
    MilkingDays_Q25 = quantile(MilkingDays, 0.25, na.rm = TRUE),
    MilkingDays_Q75 = quantile(MilkingDays, 0.75, na.rm = TRUE),

    AvgDailyYield = mean(AvgDailyYieldCow, na.rm = TRUE),
    MedianDailyYield = median(AvgDailyYieldCow, na.rm = TRUE),
    DailyYield_Q25 = quantile(AvgDailyYieldCow, 0.25, na.rm = TRUE),
    DailyYield_Q75 = quantile(AvgDailyYieldCow, 0.75, na.rm = TRUE),

    .groups = "drop"
  )

DF.SummaryByCalvingEase

Steg 11: Plotta daglig mjölkavkastning efter kalvning

Nu kommer vi att plotta daglig mjölkavkastning för varje ko i datamängden.

I stället för linjer (som kan bli svårtolkade när många kor överlappar) plottar vi varje mjölkningsdag som en punkt.
Punkterna färgas efter CalvingEase.

Observera att steg 1–3 är ganska repetitiva exempel på join/slagningar, medan steg 4 är det nya momentet med själva plottningen.

# We plot milk yield by Days in Milk (DIM) instead of calendar date.
# ggplot2 is used for plotting
# Read more here: https://ggplot2.tidyverse.org/


# DIM = number of days since calving for each cow.
# This makes cows comparable even if they calved on different dates.

# IMPORTANT:
# DF.DailyMilkPerCow currently only contains StartDate (milking date), not CalvingDate.
# We therefore re-create a calving table server-side and join it to the daily milk table,
# then calculate DIM in R after collect().

# 1) Calving table (server-side, not collected)
DF.CalvingWithOffspring_db <- con %>%
  tbl(in_catalog("basregDWH", "apiSci", "factCattleReproductionView")) %>%
  filter(
    !is.na(Calving),
    StartDate >= as.Date("2021-01-01"),
    StartDate <= as.Date("2021-01-31")
  ) %>%
  transmute(
    Farm = Farm,
    SE_Number = SE_Number,
    CalvingDate = StartDate,
    CalvingEase = CalvingEase
  )

# 2) Daily milk per cow per date (R object from Step 10)
# Make sure StartDate is Date class
DF.DailyMilkPerCow <- DF.DailyMilkPerCow %>%
  mutate(StartDate = as.Date(StartDate))

# 3) Join daily milk to calving table and calculate Days in Milk (DIM)
DF.DailyMilkWithDIM <- DF.DailyMilkPerCow %>%
  inner_join(
    DF.CalvingWithOffspring_db %>% collect(),
    by = c("SE_Number", "CalvingEase")
  ) %>%
  mutate(
    CalvingDate = as.Date(CalvingDate),
    DIM = as.integer(StartDate - CalvingDate)  # days since calving
  ) %>%
  filter(DIM >= 0, DIM <= 365) # keep the first 365 days after calving

# 4) Plot dots (easier to interpret than lines when many cows overlap)
ggplot(
  DF.DailyMilkWithDIM,
  aes(x = DIM, y = DailyTotalYield, colour = CalvingEase)
) +
  geom_point(alpha = 0.5, size = 1) +
  scale_y_continuous(limits = c(0, NA)) + # Y axis starts at 0
  labs(
    title = "Daily milk yield after calving (Days in Milk)",
    x = "Days in Milk (days since calving)",
    y = "Daily milk yield (sum of TotalYield per day)",
    colour = "CalvingEase"
  ) +
  theme_minimal()

Steg 12: Spara resultat och stäng databasanslutningen

Nu sparar vi mjölkningsdatamängden till din dator som en CSV-fil. Du kan tänka dig att vi senare kan undersöka en lämplig avgränsning för laktationens slut, eftersom vi i plottningen ser att flera djur redan har påbörjat sin nästa laktation.

Efter att ha sparat stänger vi databasanslutningen. Det är god praxis och frigör resurser både på din dator och på Basreg-servern.

# Save the milking data as a CSV file in your working directory
# The file will be saved in the folder returned by getwd()
# Read more here: https://www.rdocumentation.org/packages/utils/topics/write.csv
write.csv(DF.MilkingAfterCalving, "DF_MilkingAfterCalving.csv", row.names = FALSE)

# Close the database connection when you are done
# Using dbIsValid() avoids errors if the connection is already closed
# Read more here:
# - dbDisconnect(): https://dbi.r-dbi.org/reference/dbDisconnect.html
# - dbIsValid():    https://dbi.r-dbi.org/reference/dbIsValid.html
if (DBI::dbIsValid(con)) DBI::dbDisconnect(con)